using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using emp.Model;
using emp.IDAL;
using System.Collections;

namespace emp.SqlServerDAL
{
   public  class TaskScheduleSetup : ITaskScheduleSetup
    {
       public void Insert(TaskScheduleSetupInfo tssinfo)
       {
           int returnValue = 0;
           SqlParameter[] cellParms = null;
           string strTmp = "insert into TASK_SCHEDULE_SETUP(TSS_TBI_ID,TSS_TYPE,TSS_START_HOUR,TSS_START_MINUTE,TSS_WEEK_DAY,"
           +"TSS_MONTH_DAY,TSS_STATUS,TSS_CREATE_USER,TSS_CREATE_DATE) values("+tssinfo.TSS_TBI_ID+",'"+tssinfo.TSS_TYPE+"',"+tssinfo.TSS_START_MINUTE+","
           +" "+tssinfo.TSS_START_SECOND+","+tssinfo.TSS_WEEK_DAY+","+tssinfo.TSS_MONTH_DAY+",'"+tssinfo.TSS_STATUS+"',"+tssinfo.TSS_CREATE_USER+",getdate())";
           using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
           {
               conn.Open();
               using (SqlTransaction trans = conn.BeginTransaction())
               {
                   try
                   {
                       returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, strTmp, cellParms);
                       trans.Commit();

                   }
                   catch (SqlException err)
                   {
                       trans.Rollback();
                       System.Console.WriteLine(err.Message);
                   }


               }

           }
       }
       public void Update(TaskScheduleSetupInfo tssinfo, int tssid)
       {
           SqlParameter[] cellParms = null;
           string strTmp = "update TASK_SCHEDULE_SETUP set TSS_TBI_ID=" + tssinfo.TSS_TBI_ID + ",TSS_TYPE='" + tssinfo.TSS_TYPE + "',TSS_START_HOUR=" + tssinfo.TSS_START_MINUTE + ",TSS_START_MINUTE=" + tssinfo.TSS_START_SECOND + ",TSS_WEEK_DAY=" + tssinfo.TSS_WEEK_DAY + ",TSS_MONTH_DAY=" + tssinfo.TSS_MONTH_DAY + ",TSS_STATUS='" + tssinfo.TSS_STATUS + "',TSS_UPDATE_USER=" + tssinfo.TSS_CREATE_USER + ",TSS_UPDATE_DATE=getdate() where TSS_ID=" + tssid + "";

           int returnValue = 0;
           using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
           {
               conn.Open();
               using (SqlTransaction trans = conn.BeginTransaction())
               {
                   try
                   {

                       returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, strTmp, cellParms);
                       trans.Commit();
                   }
                   catch (SqlException err)
                   {
                       trans.Rollback();
                       System.Console.WriteLine(err.Message);
                   }
               }
               //return returnValue;
           }
       
       }
       public void Delect(int tssid)
       {
           SqlParameter[] cellParms = null;
           int returnValue = 0;
           string strTmp = "delete TASK_SCHEDULE_SETUP where tss_id="+tssid+"";
           using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
           {
               conn.Open();
               using (SqlTransaction trans = conn.BeginTransaction())
               {
                   try
                   {

                       returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, strTmp, cellParms);
                       trans.Commit();
                   }
                   catch (SqlException err)
                   {
                       trans.Rollback();
                       System.Console.WriteLine(err.Message);
                   }
               }
               // return returnValue;
           }
       }
       public IList getTask_Schedule_Setup()
       {
           string SQL_SELECT_USER_LIST = "select tss_id,TBI_VALUE,TSS_TYPE,TSS_STATUS from TASK_SCHEDULE_SETUP as tss,TASK_BASIC_INFO as tbi where tss.tss_tbi_id=tbi.tbi_id  order by tss_id desc";
           IList questlist = new ArrayList();
           SqlParameter[] parm = new SqlParameter[] { };
           //Execute a query to read the products
           using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_USER_LIST, parm))
           {
               while (rdr.Read())
               {
                   TaskScheduleSetupInfo questinfo =
                       new TaskScheduleSetupInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3));
                   questlist.Add(questinfo);
               }
           }
           return questlist;
       }


       public IList getTask_Schedule_State(string tss_type)
       {
           string SQL_SELECT_USER_LIST = "select TSS_ID,TSS_TBI_ID,TSS_TYPE,TSS_START_HOUR,"
           +"TSS_START_MINUTE,TSS_WEEK_DAY,TSS_MONTH_DAY,TSS_STATUS,TSS_CREATE_USER,"
           +"convert(varchar(10),TSS_CREATE_DATE,121),convert(int,isnull(TSS_UPDATE_USER,'')),"
           +"convert(varchar(10),isnull(TSS_UPDATE_DATE,''),121)"
           + " from TASK_SCHEDULE_SETUP where TSS_TYPE='" + tss_type + "' AND  TSS_STATUS='Y' order by tss_id desc";
           
           IList questlist = new ArrayList();
           SqlParameter[] parm = new SqlParameter[] { };
           //Execute a query to read the products
          
           using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_USER_LIST, parm))
           {
               while (rdr.Read())
               {
                   //TaskScheduleSetupInfo questinfo =
                   //    new TaskScheduleSetupInfo(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetString(2), rdr.GetInt32(3), rdr.GetInt32(4)
                   //    , rdr.GetInt32(5), rdr.GetInt32(6), rdr.GetString(7), rdr.GetInt32(8), rdr.GetString(9), rdr.GetInt32(10), rdr.GetString(11));
                   TaskScheduleSetupInfo questinfo = new TaskScheduleSetupInfo();
                   questinfo.TSS_ID = rdr.GetInt32(0);
                   questinfo.TSS_TBI_ID = rdr.GetInt32(1);
                   questinfo.TSS_TYPE = rdr.GetString(2);
                   questinfo.TSS_START_HOUR = rdr.GetInt32(3);
                   questinfo.TSS_START_MINUTE = rdr.GetInt32(4);
                   questinfo.TSS_WEEK_DAY = rdr.GetInt32(5);
                   questinfo.TSS_MONTH_DAY = rdr.GetInt32(6);
                   questinfo.TSS_STATUS = rdr.GetString(7);
                   questinfo.TSS_CREATE_USER = rdr.GetInt32(8);
                   questinfo.TSS_CREATE_DATE = rdr.GetString(9);
                   questinfo.TSS_UPDATE_USER = rdr.GetInt32(10);
                   questinfo.TSS_UPDATE_DATE = rdr.GetString(11);
                   questlist.Add(questinfo);
               }
           }
        
           return questlist;
       }



       public TaskScheduleSetupInfo getTask_Schedule_StepInfo(int tss_id)
       {
           string strTmp = "select tss_id,tss_tbi_id,tss_type,TSS_START_HOUR,TSS_START_MINUTE,tss_week_day,tss_month_day,tss_status from TASK_SCHEDULE_SETUP where tss_id =" + tss_id + "";
           TaskScheduleSetupInfo milkbrond = null;
           SqlParameter[] parm = new SqlParameter[] { };
           using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strTmp, parm))
           {
               while (rdr.Read())
               {
                   milkbrond =
                       new TaskScheduleSetupInfo(rdr.GetInt32(0),rdr.GetInt32(1),rdr.GetString(2),rdr.GetInt32(3),rdr.GetInt32(4),rdr.GetInt32(5),rdr.GetInt32(6),rdr.GetString(7));
               }
           }
           return milkbrond;
       }

       public int is_Task_One(int tbi_id)
       {
           string strSql = "select count(*) from TASK_SCHEDULE_SETUP where tss_tbi_id="+tbi_id+" and tss_status='Y'";

           int retValue = 0;
           SqlConnection Conn = new SqlConnection(ConfigurationSettings.AppSettings["SQLConnString1"]);
           SqlCommand Comm = new SqlCommand(strSql, Conn);
           try
           {
               Conn.Open();
               SqlDataReader reader = Comm.ExecuteReader();
               while (reader.Read())
               {
                   retValue = reader.GetInt32(0);
               }
               //reader.Close(); 
           }
           catch (SqlException ex)
           {
               throw;
           }
           finally
           {
               Conn.Close();
           }
           return retValue;
       }

    }
}
